#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
商务数据分析作业 - 函数式编程版本
包含员工数据分析和人口数据可视化
"""

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from functools import reduce
import warnings
warnings.filterwarnings('ignore')

# 配置matplotlib中文显示
plt.rcParams['font.sans-serif'] = ['DengXian Bold', 'Microsoft YaHei UI Bold', 'SimHei']
plt.rcParams['axes.unicode_minus'] = False

def setup_environment():
    """环境设置函数"""
    print("🔧 初始化分析环境...")
    print("✅ 环境设置完成")

def load_excel_data(filepath):
    """通用Excel数据加载函数"""
    try:
        if 'data.xlsx' in filepath:
            data = pd.read_excel(filepath, header=1)  # 员工数据第二行作为列名
        else:
            data = pd.read_excel(filepath)  # 人口数据正常读取
        print(f"📁 成功加载文件: {filepath}, 包含 {len(data)} 行数据")
        return data
    except Exception as e:
        print(f"❌ 加载文件失败: {e}")
        return None

def display_data_summary(data, name="数据"):
    """显示数据摘要信息"""
    print(f"\n{'='*20} {name}摘要信息 {'='*20}")
    print(f"数据维度: {data.shape}")
    print(f"列名: {list(data.columns)}")
    return data

def show_data_head_tail(data, head_n=10, tail_n=15):
    """显示数据头尾部分"""
    print(f"\n📋 前 {head_n} 行数据:")
    print(data.head(head_n))
    print(f"\n📋 后 {tail_n} 行数据:")
    print(data.tail(tail_n))
    return data

def clean_missing_values(data, fill_value="无"):
    """处理缺失值"""
    missing_count = data.isnull().sum().sum()
    print(f"\n🧹 发现 {missing_count} 个缺失值，用 '{fill_value}' 填充")
    return data.fillna(fill_value)

def remove_duplicates(data):
    """去除重复值"""
    original_count = len(data)
    data_clean = data.drop_duplicates()
    removed_count = original_count - len(data_clean)
    print(f"🗑️ 删除了 {removed_count} 条重复记录")
    return data_clean

def count_active_employees(data):
    """统计在职员工数量"""
    active_count = len(data[data['在职状态'] == '在职'])
    print(f"👥 在职员工数量: {active_count} 人")
    return active_count

def parse_salary_data(salary_series):
    """解析工资数据的辅助函数"""
    return pd.to_numeric(
        salary_series.astype(str).str.replace(',', '').str.replace(' ', ''),
        errors='coerce'
    ).dropna()

def calculate_salary_stats(data):
    """计算工资统计信息"""
    print("\n💰 工资统计分析:")
    
    # 过滤有效的工资数据
    valid_salary_data = data[data['应发工资'].notna() & (data['应发工资'] != "无")]
    
    if not valid_salary_data.empty:
        salary_values = parse_salary_data(valid_salary_data['应发工资'])
        if not salary_values.empty:
            stats = {
                '最大值': salary_values.max(),
                '最小值': salary_values.min(),
                '平均值': salary_values.mean(),
                '中位数': salary_values.median()
            }
            
            for key, value in stats.items():
                print(f"   应发工资{key}: {value:.2f} 元")
            
            return stats
    
    print("   无有效工资数据")
    return {}

def create_new_employee_record():
    """创建新员工记录"""
    return {
        '序号': 993,
        '工号': 'GH993',
        '姓名': '王五',
        '性别': '男',
        '手机号': '13777777777',
        '出生年月': '19880710',
        '入职日期': '20240115',
        '年龄': 36,
        '在职状态': '在职',
        '工龄': 1,
        '籍贯': '广东省',
        '学历': '大专',
        '基本工资': 5500,
        '应发工资': 7800
    }

def add_employee(data, new_employee_data):
    """添加新员工"""
    new_row = pd.DataFrame([new_employee_data])
    result = pd.concat([data, new_row], ignore_index=True)
    print(f"➕ 已添加新员工: {new_employee_data['姓名']} (工号: {new_employee_data['工号']})")
    return result

def update_employee_phone(data, employee_id, new_phone):
    """更新员工手机号"""
    mask = data['序号'] == employee_id
    if mask.any():
        data.loc[mask, '手机号'] = new_phone
        print(f"📱 已更新序号 {employee_id} 员工的手机号为: {new_phone}")
    else:
        print(f"⚠️ 未找到序号为 {employee_id} 的员工")
    return data

def filter_employees_for_removal(data):
    """筛选需要删除的员工"""
    condition = (data['年龄'] > 55) & (data['在职状态'] == '离职') & (data['性别'] == '男')
    to_remove = data[condition]
    remaining = data[~condition]
    
    print(f"🗑️ 符合删除条件的员工数量: {len(to_remove)}")
    if len(to_remove) > 0:
        print("   删除的员工信息:")
        for _, row in to_remove.iterrows():
            print(f"   - {row['姓名']} (序号: {row['序号']}, 年龄: {row['年龄']})")
    
    return remaining

def group_analysis_by_education(data):
    """按学历分组分析"""
    print("\n📚 按学历分组的工资分析:")
    
    # 过滤有效数据
    valid_data = data[data['应发工资'].notna() & (data['应发工资'] != "无")].copy()
    
    if not valid_data.empty:
        valid_data['salary_numeric'] = parse_salary_data(valid_data['应发工资'])
        
        # 分组统计
        education_stats = valid_data.groupby('学历')['salary_numeric'].agg([
            ('平均工资', 'mean'),
            ('人数', 'count'),
            ('最高工资', 'max'),
            ('最低工资', 'min')
        ]).round(2)
        
        print(education_stats)
        return education_stats
    
    print("   无有效数据进行分析")
    return pd.DataFrame()

def group_analysis_by_gender(data):
    """按性别分组分析"""
    print("\n👨👩 按性别分组的工资分析:")
    
    # 过滤有效数据
    valid_data = data[data['应发工资'].notna() & (data['应发工资'] != "无")].copy()
    
    if not valid_data.empty:
        valid_data['salary_numeric'] = parse_salary_data(valid_data['应发工资'])
        
        # 分组统计
        gender_stats = valid_data.groupby('性别')['salary_numeric'].agg([
            ('平均工资', 'mean'),
            ('人数', 'count'),
            ('最高工资', 'max'),
            ('最低工资', 'min')
        ]).round(2)
        
        print(gender_stats)
        return gender_stats
    
    print("   无有效数据进行分析")
    return pd.DataFrame()

def export_to_excel(data, filename='dataoutput.xlsx'):
    """导出数据到Excel"""
    try:
        data.to_excel(filename, index=False)
        print(f"💾 数据已成功导出到: {filename}")
    except Exception as e:
        print(f"❌ 导出失败: {e}")

# 可视化相关函数
def create_urban_rural_bar_chart(population_data):
    """创建城乡人口对比柱状图"""
    print("📊 绘制城乡人口对比柱状图...")
    
    fig, ax = plt.subplots(figsize=(15, 8))
    
    years = population_data['年份'].str.replace('年', '')
    x_positions = np.arange(len(years))
    bar_width = 0.4
    
    # 创建柱状图
    urban_bars = ax.bar(x_positions - bar_width/2, population_data['城镇人口'], 
                       bar_width, label='城镇人口', color='#1f77b4', alpha=0.8)
    rural_bars = ax.bar(x_positions + bar_width/2, population_data['乡村人口'], 
                       bar_width, label='乡村人口', color='#ff7f0e', alpha=0.8)
    
    # 设置图表属性
    ax.set_xlabel('年份', fontsize=14)
    ax.set_ylabel('人口数量（万人）', fontsize=14)
    ax.set_title('2014-2022年城乡人口变化对比分析', fontsize=16, fontweight='bold')
    ax.set_xticks(x_positions)
    ax.set_xticklabels(years, rotation=0)
    ax.legend(fontsize=12)
    ax.grid(axis='y', alpha=0.3)
    
    # 添加数值标签
    def add_value_labels(bars):
        for bar in bars:
            height = bar.get_height()
            ax.text(bar.get_x() + bar.get_width()/2., height + 500,
                   f'{int(height)}', ha='center', va='bottom', fontsize=10)
    
    add_value_labels(urban_bars)
    add_value_labels(rural_bars)
    
    plt.tight_layout()
    plt.savefig('城乡人口对比图_v3.png', dpi=300, bbox_inches='tight')
    plt.show()

def create_gender_pie_chart(population_data):
    """创建性别比例饼图"""
    print("🥧 绘制2022年男女人口比例饼图...")
    
    # 获取2022年数据
    data_2022 = population_data[population_data['年份'] == '2022年']
    
    if not data_2022.empty:
        male_population = data_2022['男性人口'].iloc[0]
        female_population = data_2022['女性人口'].iloc[0]
        total_population = male_population + female_population
        
        fig, ax = plt.subplots(figsize=(10, 10))
        
        # 数据和标签
        sizes = [male_population, female_population]
        labels = [f'男性\n{male_population}万人', f'女性\n{female_population}万人']
        colors = ['#66b3ff', '#ff99cc']
        explode = (0.1, 0.1)
        
        # 创建饼图
        wedges, texts, autotexts = ax.pie(sizes, labels=labels, colors=colors, 
                                         autopct=lambda pct: f'{pct:.2f}%',
                                         startangle=90, explode=explode,
                                         shadow=True, textprops={'fontsize': 12})
        
        ax.set_title('2022年全国男女人口比例分布', fontsize=16, fontweight='bold', pad=20)
        
        # 美化百分比文本
        for autotext in autotexts:
            autotext.set_color('white')
            autotext.set_fontweight('bold')
            autotext.set_fontsize(14)
        
        plt.savefig('2022年性别比例图_v3.png', dpi=300, bbox_inches='tight')
        plt.show()

def create_population_trend_line(population_data):
    """创建人口变化趋势折线图"""
    print("📈 绘制城乡人口变化趋势图...")
    
    fig, ax = plt.subplots(figsize=(14, 8))
    
    years = [int(year.replace('年', '')) for year in population_data['年份']]
    
    # 绘制折线图
    ax.plot(years, population_data['城镇人口'], marker='o', linewidth=3, 
            markersize=8, label='城镇人口', color='#2ca02c', markerfacecolor='white', 
            markeredgewidth=2)
    ax.plot(years, population_data['乡村人口'], marker='^', linewidth=3, 
            markersize=8, label='乡村人口', color='#d62728', markerfacecolor='white', 
            markeredgewidth=2)
    
    # 设置图表属性
    ax.set_xlabel('年份', fontsize=14)
    ax.set_ylabel('人口数量（万人）', fontsize=14)
    ax.set_title('2014-2022年城乡人口变化趋势分析', fontsize=16, fontweight='bold')
    ax.legend(fontsize=12, loc='center right')
    ax.grid(True, alpha=0.3, linestyle='--')
    
    # 添加趋势填充
    ax.fill_between(years, population_data['城镇人口'], alpha=0.2, color='#2ca02c')
    ax.fill_between(years, population_data['乡村人口'], alpha=0.2, color='#d62728')
    
    # 设置y轴范围
    ax.set_ylim(bottom=0)
    
    plt.tight_layout()
    plt.savefig('人口变化趋势_v3.png', dpi=300, bbox_inches='tight')
    plt.show()

def create_correlation_scatter_plot(population_data):
    """创建相关性散点图"""
    print("🎯 绘制城镇人口与总人口相关性散点图...")
    
    fig, ax = plt.subplots(figsize=(12, 8))
    
    # 创建散点图
    years = [int(year.replace('年', '')) for year in population_data['年份']]
    colors = plt.cm.viridis(np.linspace(0, 1, len(years)))
    
    for i, (total, urban, year) in enumerate(zip(population_data['年末总人口'], 
                                                population_data['城镇人口'], years)):
        ax.scatter(total, urban, c=[colors[i]], s=150, alpha=0.8, 
                  edgecolors='black', linewidth=1, label=f'{year}年' if i < 3 else "")
    
    # 计算并绘制回归线
    z = np.polyfit(population_data['年末总人口'], population_data['城镇人口'], 1)
    p = np.poly1d(z)
    ax.plot(population_data['年末总人口'], p(population_data['年末总人口']), 
            "r--", alpha=0.8, linewidth=2, label='趋势线')
    
    # 计算相关系数
    correlation = np.corrcoef(population_data['年末总人口'], population_data['城镇人口'])[0, 1]
    
    # 设置图表属性
    ax.set_xlabel('年末总人口（万人）', fontsize=14)
    ax.set_ylabel('城镇人口（万人）', fontsize=14)
    ax.set_title(f'城镇人口与年末总人口相关性分析 (相关系数: {correlation:.4f})', 
                fontsize=16, fontweight='bold')
    ax.grid(True, alpha=0.3)
    ax.legend(fontsize=10)
    
    # 添加文本注释
    ax.text(0.05, 0.95, f'相关系数: {correlation:.4f}', 
            transform=ax.transAxes, fontsize=12, 
            bbox=dict(boxstyle="round,pad=0.3", facecolor="yellow", alpha=0.7))
    
    plt.tight_layout()
    plt.savefig('城镇人口相关性_v3.png', dpi=300, bbox_inches='tight')
    plt.show()

def main():
    """主程序执行函数"""
    print("🚀 开始执行商务数据分析任务 (函数式编程版本)")
    print("="*80)
    
    # 环境设置
    setup_environment()
    
    # ===============================
    # 任务一：员工数据分析
    # ===============================
    print(f"\n{'='*25} 任务一：员工数据分析 {'='*25}")
    
    # 数据处理流水线
    employee_data = (
        load_excel_data('data.xlsx')
        .pipe(display_data_summary, "员工数据")
        .pipe(show_data_head_tail)
        .pipe(clean_missing_values)
        .pipe(remove_duplicates)
    )
    
    # 统计分析
    count_active_employees(employee_data)
    calculate_salary_stats(employee_data)
    
    # 数据操作
    new_employee = create_new_employee_record()
    employee_data = add_employee(employee_data, new_employee)
    employee_data = update_employee_phone(employee_data, 990, '15012345678')
    employee_data = filter_employees_for_removal(employee_data)
    
    # 分组分析
    group_analysis_by_education(employee_data)
    group_analysis_by_gender(employee_data)
    
    # 导出数据
    export_to_excel(employee_data)
    
    # ===============================
    # 任务二：人口数据可视化
    # ===============================
    print(f"\n{'='*25} 任务二：人口数据可视化 {'='*25}")
    
    # 加载人口数据
    population_data = load_excel_data('population.xlsx')
    display_data_summary(population_data, "人口数据")
    
    # 生成各种图表
    visualization_functions = [
        create_urban_rural_bar_chart,
        create_gender_pie_chart,
        create_population_trend_line,
        create_correlation_scatter_plot
    ]
    
    # 使用函数式编程方式执行可视化
    list(map(lambda func: func(population_data), visualization_functions))
    
    print("\n🎉 所有分析任务已完成！")
    print("📁 生成的文件:")
    print("   - dataoutput.xlsx (处理后的员工数据)")
    print("   - 城乡人口对比图_v3.png")
    print("   - 2022年性别比例图_v3.png")
    print("   - 人口变化趋势_v3.png")
    print("   - 城镇人口相关性_v3.png")

if __name__ == "__main__":
    main() 